﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using ND.Tool;
using System.Data;

namespace EZ.Open.AutoCode
{
    //存储过程相关
    public class ProcedureHelper
    {
        private static string m_cs = ConfigInfo.WebCS;

        public static List<ProcedureInfo> GetProcedureInfos(string table)
        {
            //有参数的存储过程
            DataBase db = SQLHelper.CreateData(m_cs);
            //DataSet ds = db.ExecuteDataSet("SELECT a.name AS table_name, ISNULL(g.value, '-') AS note FROM sys.tables AS a LEFT OUTER JOIN sys.extended_properties AS g ON a.object_id = g.major_id AND g.minor_id = 0 WHERE (a.object_id = (SELECT object_id FROM sys.tables WHERE (name = '@table_name')))");
            string cmd = @"select Pr_Name as [存储过程], [参数]=stuff((select '，'+[Parameter]
                                            from (
                                            select Pr.Name as Pr_Name,parameter.name +' ' +Type.Name + ' ('+convert(varchar(32),parameter.max_length)+')' as Parameter
                                            from sys.procedures Pr left join
                                            sys.parameters parameter on Pr.object_id = parameter.object_id
                                            inner join sys.types Type on parameter.system_type_id = Type.system_type_id
                                            where type = 'P'
                                            ) t where Pr_Name=tb.Pr_Name for xml path('')), 1, 1, '')
                                            from (
                                            select Pr.Name as Pr_Name,parameter.name +' ' +Type.Name + ' ('+convert(varchar(32),parameter.max_length)+')' as Parameter
                                            from sys.procedures Pr left join
                                            sys.parameters parameter on Pr.object_id = parameter.object_id
                                            inner join sys.types Type on parameter.system_type_id = Type.system_type_id
                                            where type = 'P'
                                            )tb
                                            where Pr_Name not like 'sp_%' and Pr_Name like 'EZ_a_{tablename}%'
                                            group by Pr_Name
                                            order by Pr_Name".Replace("{tablename}", string.IsNullOrEmpty(table) ? "" : table+ "_");
            DataSet ds = db.ExecuteDataSet(cmd);

            List<ProcedureInfo> list = new List<ProcedureInfo>();
            foreach (DataRow dataRow in ds.Tables[0].Rows)
            {
                ProcedureInfo tableInfo = new ProcedureInfo();
                tableInfo.Name = Convert.ToString(dataRow["存储过程"]);
                tableInfo.ToMehtodName();
                tableInfo.ToEntityName(); 
                tableInfo.ToTableName();
                tableInfo.ToApiName();
                tableInfo.Params = ProcedureParam.GetParams( Convert.ToString(dataRow["参数"]));
                list.Add(tableInfo);
            }
            //无参数的存储过程
            DataSet ds1 = db.ExecuteDataSet(@"select Pr.Name as [存储过程], [参数]='' from sys.procedures Pr where Pr.Name like 'EZ_a_{tablename}%'"
                .Replace("{tablename}", string.IsNullOrEmpty(table) ? "" : table + "_"));

            foreach (DataRow dataRow in ds1.Tables[0].Rows)
            {
                ProcedureInfo tableInfo = new ProcedureInfo();
                tableInfo.Name = Convert.ToString(dataRow["存储过程"]);
                tableInfo.ToMehtodName();
                tableInfo.ToEntityName();
                tableInfo.ToTableName();
                tableInfo.ToApiName();
                tableInfo.Params = ProcedureParam.GetParams(Convert.ToString(dataRow["参数"]));
                var find = (from o in list where o.Name == tableInfo.Name select o).FirstOrDefault();
                if(find == null)
                    list.Add(tableInfo);
            }
            return list;
        }
    }

    public class ProcedureInfo
    {
        public string Name{get;set;}
        public string TableName { get; set; }
        public string EntityName { get; set; }
        public string MethodName { get; set; }
        public string ApiName { get; set; }
        public List<ProcedureParam> Params{get;set;}

        public void ToMehtodName()
        {
            string[] ss = Name.Replace("EZ_a_", "").Split('_'); List<string> rs = new List<string>();
            foreach (var s in ss)
            {
                rs.Add(System.Threading.Thread.CurrentThread.CurrentCulture.TextInfo.ToTitleCase(s.ToLower()));
            }
            MethodName = string.Join("",rs.ToArray()) ;
        }

        public void ToTableName()
        {
            string[] ss = Name.Replace("EZ_a_", "").Split('_'); List<string> rs = new List<string>();
            TableName = "EZ_" + ss[0];
        }

        public void ToEntityName()
        {
            string[] ss = Name.Replace("EZ_a_", "").Split('_'); List<string> rs = new List<string>();
            EntityName = ss[0];
        }

        public void ToApiName()
        {
            ApiName = "ez." + Name.Replace("EZ_a_", "").Replace("_", ".").ToLower();
        }
    }

    public class ProcedureParam
    {
        public string SqlType{get;set;}
        public string Name{get;set;}
        public string FormatName { get; set; }
        public string ClassType{get;set;} 

        public static List<ProcedureParam> GetParams(string formatstr)
        {
            List<ProcedureParam> rs = new List<ProcedureParam>();
            //@name1 int (4)，@createtime datetime (8)，@float1 float (8)，@bit1 bit (1)，@long1 bigint (8)，@name2 varchar (100)
            if (formatstr != "")
            {
                string[] ps = formatstr.Split('，');
                if (ps != null)
                {
                    foreach (var p in ps)
                    {
                        string[] infos = p.Split(' ');
                        var o = new ProcedureParam();
                        o.Name = infos[0].Replace("@", "");
                        o.SqlType = infos[1];
                        o.ClassType = SqlTypeToClassType(o.SqlType);
                        o.FormatName = System.Threading.Thread.CurrentThread.CurrentCulture.TextInfo.ToTitleCase(o.Name);
                        rs.Add(o);
                    }
                }
            }
            return rs;
        }

        public static string SqlTypeToClassType(string sqltype)
        {
            if (sqltype == "int")
                return "int";
            else if (sqltype == "datetime")
                return "DateTime";
            else if (sqltype == "float")
                return "double";
            else if (sqltype == "bit")
                return "bool";
            else if (sqltype == "bigint")
                return "long";
            else
                return "string";
        }
    }
}
